// Package query prepares all needed DB statements during initiliazation and makes them available
// through the Get function.
package query

import (
    "config"
    "database/sql"
    _ "github.com/bmizerany/pq"
    "log"
    "os"
)

var prepared map[string]map[string]*sql.Stmt

func genQueries(name string) map[string]string {
    return map[string]string{
        "getImage": `
SELECT size, full_x, full_y, thumb_x,
    thumb_y, thumb_file, img_file
FROM images WHERE report_id IS NULL AND int_id = $1`,

        "topPageStmt": `
WITH thread_group AS (
    SELECT DISTINCT last_updated
    FROM ` + name + `_threads
    ORDER BY last_updated DESC LIMIT $2 OFFSET $1
), unabbrev_page AS (
    SELECT 
       *,
       row_number() over (PARTITION BY ` + name + `_threads.last_updated 
                          ORDER BY time) AS post_seq
    FROM ` + name + `_threads
    INNER JOIN ` + name + ` ON ` + name + `_threads.op = ` + name + `.parent
    INNER JOIN thread_group
    ON ` + name + `_threads.last_updated = thread_group.last_updated
    ORDER BY ` + name + `_threads.last_updated DESC, time
)
SELECT int_id, post_id, ghost_id, time, name, trip, subject, mail, poster_uid,
       body, spoiler, post_deleted, parent, image, img_name, img_url,
       highlight, auth, sticky, locked, thread_deleted, expired, all_posts,
       image_posts, thread_url, NULL
FROM unabbrev_page WHERE (post_seq = 1 OR post_seq > all_posts - $3)
                   AND report_id IS NULL`,

        "ghostTopPageStmt": `
WITH thread_group AS (
    SELECT DISTINCT last_updated
    FROM ` + name + `_threads
    ORDER BY last_updated DESC LIMIT $2 OFFSET $1
), unabbrev_page AS (
    SELECT 
       *,
       row_number() over (PARTITION BY ` + name + `_threads.last_updated 
                          ORDER BY time) AS post_seq
    FROM ` + name + `_threads
    INNER JOIN ` + name + ` ON ` + name + `_threads.op = ` + name + `.parent
    INNER JOIN thread_group
    ON ` + name + `_threads.last_updated = thread_group.last_updated
    ORDER BY ` + name + `_threads.ghost_updated DESC, time
)
SELECT int_id, post_id, ghost_id, time, name, trip, subject, mail, poster_uid,
       body, spoiler, post_deleted, parent, image, img_name, img_url,
       highlight, auth, sticky, locked, thread_deleted, expired, all_posts,
       image_posts, thread_url, NULL
FROM unabbrev_page WHERE (post_seq = 1 OR post_seq > all_posts - $3)
                   AND report_id IS NULL`,

        "fullThread": `
SELECT ` + name + `.int_id, post_id, ghost_id, time, name, trip, subject, mail, poster_uid,
       body, spoiler, post_deleted, parent, image, img_name, img_url, highlight, auth,
       sticky, locked, thread_deleted, expired, 0, 0, thread_url, NULL
FROM ` + name + `
INNER JOIN ` + name + `_threads ON ` + name + `.parent = ` + name + `_threads.op
WHERE parent = $1 AND report_id IS NULL
ORDER BY time;`,

        "insertPost": `
INSERT INTO ` + name + ` (post_id, ghost_id, time, name, trip, subject, mail, poster_uid, body, spoiler, parent, image, img_name, img_url, ip, passwd, highlight, auth)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13, $14, $15, $16, $17, $18)
RETURNING post_id, ghost_id`,

        "boardPostCount": ` SELECT rows FROM table_count WHERE name = $1`,
        "reportPost":     `SELECT * FROM ` + name + `_report_post($1, $2, $3)`,

        "blockAssocImg": `
WITH img_id AS ( SELECT image from ` + name + ` WHERE int_id = $1)
UPDATE images SET report_id = $2 WHERE int_id = $1`,

        "nullPost": `
UPDATE ` + name + `
SET name = '', trip = '',  subject = '', ts_subject = '', mail = '',
    poster_uid = '', body = '', ts_body = '', img_name = '', img_url = '',
    report_id = $1`,
    }
}

// public function for running prepared queries.
func Get(boardName, key string) *sql.Stmt {
    if _, ok := prepared[boardName]; !ok {
        log.Panic("No query map for ", boardName)
    }
    if _, ok := prepared[boardName][key]; !ok {
        log.Panic("No query named " + key + " for " + boardName)
    }
    return prepared[boardName][key]
}

// utility function for checking unmanagable errors.
func chk(e error) {
    if e != nil {
        log.Panic(e)
    }
}

// Connect to DB and prepare queries for all boards.
func init() {
    cfg := config.Defaults
    os.Setenv("PGHOST", cfg.Pghost) // pq.go will choke with cryptic errors if PGHOST is not set.
    dbh, err := sql.Open("postgres", "dbname="+cfg.DbName+" sslmode=disable")
    chk(err)

    prepared = map[string]map[string]*sql.Stmt{}

    for name, b := range config.Boards {
        prep := map[string]*sql.Stmt{}
        for k, v := range genQueries(b.TableName) {
            log.Printf("Preparing SQL statement %s for %s", k, name)
            stmt, err := dbh.Prepare(v)
            chk(err)
            prep[k] = stmt
        }
        log.Printf("Adding query map for %s", name)
        prepared[b.TableName] = prep
    }
}
